package com.insight.datasource.adapter;

import com.insight.common.util.EncryptionUtil;
import com.insight.entity.DataSource;
import com.insight.entity.MetadataColumn;
import com.insight.entity.MetadataIndex;
import com.insight.entity.MetadataTable;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;

/**
 * T058: MySQL元数据提取器实现
 * 使用MySQL INFORMATION_SCHEMA系统表提取元数据
 *
 * <p>支持的MySQL版本: 5.7+, 8.0+
 *
 * <p>核心查询表:
 * <ul>
 *   <li>INFORMATION_SCHEMA.TABLES - 表元数据</li>
 *   <li>INFORMATION_SCHEMA.COLUMNS - 列元数据</li>
 *   <li>INFORMATION_SCHEMA.STATISTICS - 索引元数据</li>
 * </ul>
 *
 * @author Insight Team
 */
@Slf4j
public class MySQLMetadataExtractor implements MetadataExtractor {

    /**
     * 连接超时时间(秒)
     */
    private static final int CONNECTION_TIMEOUT = 10;

    /**
     * 查询超时时间(秒)
     */
    private static final int QUERY_TIMEOUT = 30;

    @Override
    public List<MetadataTable> extractTables(DataSource dataSource, String schemaFilter) {
        log.info("开始提取MySQL表列表: dataSource={}, schema={}", dataSource.getName(), schemaFilter);

        List<MetadataTable> tables = new ArrayList<>();
        String schema = schemaFilter != null ? schemaFilter : dataSource.getDatabaseName();

        String sql = """
                SELECT
                    TABLE_SCHEMA,
                    TABLE_NAME,
                    TABLE_TYPE,
                    ENGINE,
                    TABLE_ROWS,
                    AVG_ROW_LENGTH,
                    DATA_LENGTH,
                    INDEX_LENGTH,
                    TABLE_COLLATION,
                    TABLE_COMMENT,
                    CREATE_TIME,
                    UPDATE_TIME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = ?
                  AND TABLE_TYPE = 'BASE TABLE'
                ORDER BY TABLE_NAME
                """;

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);
            pstmt.setString(1, schema);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    MetadataTable table = new MetadataTable();
                    table.setDataSource(dataSource);
                    table.setTableSchema(rs.getString("TABLE_SCHEMA"));
                    table.setTableName(rs.getString("TABLE_NAME"));
                    String tableType = rs.getString("TABLE_TYPE");
                    table.setTableType("BASE TABLE".equals(tableType) ? "TABLE" : tableType);
                    table.setRowCount(rs.getLong("TABLE_ROWS"));

                    // avgRowLength字段类型是Integer
                    Long avgRowLen = rs.getLong("AVG_ROW_LENGTH");
                    if (!rs.wasNull()) {
                        // table.setAvgRowLength removed - field does not exist in schema
                    }

                    // table.setDataLength removed - field does not exist in schema
                    // table.setIndexLength removed - field does not exist in schema
                    table.setTableComment(rs.getString("TABLE_COMMENT"));
                    // table.setCreateTime removed - field does not exist in schema
                    tables.add(table);
                }
            }

            log.info("成功提取MySQL表列表: dataSource={}, tableCount={}", dataSource.getName(), tables.size());
            return tables;

        } catch (SQLException e) {
            log.error("提取MySQL表列表失败: dataSource={}, error={}", dataSource.getName(), e.getMessage(), e);
            throw new RuntimeException("提取MySQL表列表失败: " + e.getMessage(), e);
        }
    }

    @Override
    public List<MetadataColumn> extractColumns(DataSource dataSource, MetadataTable table) {
        log.info("开始提取MySQL列信息: table={}", table.getFullName());

        List<MetadataColumn> columns = new ArrayList<>();

        String sql = """
                SELECT
                    COLUMN_NAME,
                    ORDINAL_POSITION,
                    COLUMN_DEFAULT,
                    IS_NULLABLE,
                    DATA_TYPE,
                    CHARACTER_MAXIMUM_LENGTH,
                    NUMERIC_PRECISION,
                    NUMERIC_SCALE,
                    COLUMN_TYPE,
                    COLUMN_KEY,
                    EXTRA,
                    COLUMN_COMMENT
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = ?
                  AND TABLE_NAME = ?
                ORDER BY ORDINAL_POSITION
                """;

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);
            pstmt.setString(1, table.getTableSchema());
            pstmt.setString(2, table.getTableName());

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    MetadataColumn column = new MetadataColumn();
                    column.setTable(table);
                    column.setColumnName(rs.getString("COLUMN_NAME"));
                    column.setColumnPosition(rs.getInt("ORDINAL_POSITION"));
                    column.setDefaultValue(rs.getString("COLUMN_DEFAULT"));
                    column.setNullable("YES".equals(rs.getString("IS_NULLABLE")));
                    column.setDataType(rs.getString("DATA_TYPE"));

                    // 字符类型长度
                    Long charLength = rs.getLong("CHARACTER_MAXIMUM_LENGTH");
                    if (!rs.wasNull()) {
                        column.setColumnLength(charLength.intValue());
                    }

                    // 数值类型精度和小数位
                    Integer numericPrecision = rs.getInt("NUMERIC_PRECISION");
                    if (!rs.wasNull()) {
                        column.setColumnPrecision(numericPrecision);
                    }

                    Integer numericScale = rs.getInt("NUMERIC_SCALE");
                    if (!rs.wasNull()) {
                        column.setColumnScale(numericScale);
                    }

                    // 主键判断
                    String columnKey = rs.getString("COLUMN_KEY");
                    column.setIsPrimaryKey("PRI".equals(columnKey));

                    // 自增判断
                    String extra = rs.getString("EXTRA");

                    column.setColumnComment(rs.getString("COLUMN_COMMENT"));

                    columns.add(column);
                }
            }

            log.info("成功提取MySQL列信息: table={}, columnCount={}", table.getFullName(), columns.size());
            return columns;

        } catch (SQLException e) {
            log.error("提取MySQL列信息失败: table={}, error={}", table.getFullName(), e.getMessage(), e);
            throw new RuntimeException("提取MySQL列信息失败: " + e.getMessage(), e);
        }
    }

    @Override
    public List<MetadataIndex> extractIndexes(DataSource dataSource, MetadataTable table) {
        log.info("开始提取MySQL索引信息: table={}", table.getFullName());

        Map<String, MetadataIndex> indexMap = new LinkedHashMap<>();

        String sql = """
                SELECT
                    INDEX_NAME,
                    NON_UNIQUE,
                    COLUMN_NAME,
                    SEQ_IN_INDEX,
                    INDEX_TYPE,
                    INDEX_COMMENT
                FROM INFORMATION_SCHEMA.STATISTICS
                WHERE TABLE_SCHEMA = ?
                  AND TABLE_NAME = ?
                ORDER BY INDEX_NAME, SEQ_IN_INDEX
                """;

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);
            pstmt.setString(1, table.getTableSchema());
            pstmt.setString(2, table.getTableName());

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    String columnName = rs.getString("COLUMN_NAME");
                    boolean nonUnique = rs.getInt("NON_UNIQUE") == 1;
                    String indexType = rs.getString("INDEX_TYPE");
                    String indexComment = rs.getString("INDEX_COMMENT");

                    // 获取或创建索引对象
                    MetadataIndex index = indexMap.computeIfAbsent(indexName, k -> {
                        MetadataIndex newIndex = new MetadataIndex();
                        newIndex.setTable(table);
                        newIndex.setIndexName(indexName);
                        newIndex.setIsUnique(!nonUnique);
                        newIndex.setIndexType(indexType);
                        newIndex.setIndexComment(indexComment);
                        return newIndex;
                    });

                    // 追加列名(复合索引)
                    String existingColumns = index.getColumnNames();
                    if (existingColumns == null || existingColumns.isEmpty()) {
                        index.setColumnNames(columnName);
                    } else {
                        index.setColumnNames(existingColumns + "," + columnName);
                    }
                }
            }

            List<MetadataIndex> indexes = new ArrayList<>(indexMap.values());

            log.info("成功提取MySQL索引信息: table={}, indexCount={}", table.getFullName(), indexes.size());
            return indexes;

        } catch (SQLException e) {
            log.error("提取MySQL索引信息失败: table={}, error={}", table.getFullName(), e.getMessage(), e);
            throw new RuntimeException("提取MySQL索引信息失败: " + e.getMessage(), e);
        }
    }

    @Override
    public boolean testConnection(DataSource dataSource) {
        log.info("测试MySQL连接: dataSource={}", dataSource.getName());

        String sql = "SELECT 1";

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(CONNECTION_TIMEOUT);

            try (ResultSet rs = pstmt.executeQuery()) {
                boolean result = rs.next() && rs.getInt(1) == 1;
                log.info("MySQL连接测试{}: dataSource={}",
                        result ? "成功" : "失败", dataSource.getName());
                return result;
            }

        } catch (SQLException e) {
            log.error("MySQL连接测试失败: dataSource={}, error={}",
                    dataSource.getName(), e.getMessage());
            return false;
        }
    }

    @Override
    public String getDatabaseVersion(DataSource dataSource) {
        log.info("获取MySQL版本信息: dataSource={}", dataSource.getName());

        String sql = "SELECT VERSION()";

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    String version = rs.getString(1);
                    log.info("MySQL版本: dataSource={}, version={}", dataSource.getName(), version);
                    return version;
                }
            }

        } catch (SQLException e) {
            log.error("获取MySQL版本失败: dataSource={}, error={}",
                    dataSource.getName(), e.getMessage());
        }

        return "未知版本";
    }

    @Override
    public String getCurrentSchema(DataSource dataSource) {
        log.info("获取MySQL当前数据库: dataSource={}", dataSource.getName());

        String sql = "SELECT DATABASE()";

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    String schema = rs.getString(1);
                    log.info("MySQL当前数据库: dataSource={}, schema={}", dataSource.getName(), schema);
                    return schema;
                }
            }

        } catch (SQLException e) {
            log.error("获取MySQL当前数据库失败: dataSource={}, error={}",
                    dataSource.getName(), e.getMessage());
        }

        return null;
    }

    /**
     * 创建MySQL数据库连接
     *
     * @param dataSource 数据源配置
     * @return JDBC连接
     * @throws SQLException 连接异常
     */
    private Connection createConnection(DataSource dataSource) throws SQLException {
        String jdbcUrl = buildJdbcUrl(dataSource);

        Properties props = new Properties();
        props.setProperty("user", dataSource.getUsername() != null ? dataSource.getUsername() : "");
        // 解密密码 - 使用passwordEncrypted字段
        String decryptedPassword = "";
        if (dataSource.getPasswordEncrypted() != null && !dataSource.getPasswordEncrypted().isEmpty()) {
            try {
                decryptedPassword = EncryptionUtil.decrypt(dataSource.getPasswordEncrypted());
            } catch (Exception e) {
                log.error("解密密码失败: dataSource={}, error={}", dataSource.getName(), e.getMessage());
                throw new SQLException("密码解密失败: " + e.getMessage(), e);
            }
        }
        props.setProperty("password", decryptedPassword);
        props.setProperty("useUnicode", "true");
        props.setProperty("characterEncoding", "UTF-8");
        props.setProperty("serverTimezone", "Asia/Shanghai");
        props.setProperty("useSSL", "false");
        props.setProperty("allowPublicKeyRetrieval", "true");

        log.debug("创建MySQL连接: url={}", jdbcUrl);

        DriverManager.setLoginTimeout(CONNECTION_TIMEOUT);
        return DriverManager.getConnection(jdbcUrl, props);
    }

    /**
     * 构建MySQL JDBC URL
     *
     * @param dataSource 数据源配置
     * @return JDBC URL
     */
    private String buildJdbcUrl(DataSource dataSource) {
        return String.format("jdbc:mysql://%s:%d/%s",
                dataSource.getHost(),
                dataSource.getPort(),
                dataSource.getDatabaseName());
    }
}
